Imports Microsoft.VisualBasic
Imports System.Collections.Specialized
Imports System.Configuration.Provider
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security.Permissions
Imports System.Web
Imports System.Web.Configuration
Imports System.Web.Util

<SqlClientPermission(SecurityAction.Demand, Unrestricted:=True)> _
Public Class SqlMembershipsAndRolesProvider
    Inherits MembershipsAndRolesProvider

    ' Fields
    Private _applicationName As String
    Private _connectionString As String
    Private _connectionStringName As String
    Private _CommandTimeout As Integer


#Region "Initialized Sub and Properties"

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)

        'Verify that config isn't null
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        'Assign the provider a default name if it doesn't have one
        If String.IsNullOrEmpty(name) Then
            name = "SqlMembershipsAndRolesProvider"
        End If

        'Add a default "description" attribute to config if the
        'Attribute(doesn) 't exist or is empty
        If String.IsNullOrEmpty(config.Item("description")) Then
            config.Remove("description")
            config.Add("description", "SQL MembershipsAndRoles Provider")
        End If

        'Call the base class's Initialize method
        MyBase.Initialize(name, config)

        'Initialize _applicationName
        _applicationName = config("applicationName")

        If (String.IsNullOrEmpty(_applicationName)) Then _applicationName = "/"

        config.Remove("applicationName")

        'Initialize _connectionString
        _connectionStringName = config("connectionStringName")

        If (String.IsNullOrEmpty(_connectionStringName)) Then _
            Throw New ProviderException("Empty or missing connectionStringName")

        If WebConfigurationManager.ConnectionStrings(_connectionStringName) Is Nothing Then _
            Throw New ProviderException("Missing connection string")

        _connectionString = WebConfigurationManager.ConnectionStrings(_connectionStringName).ConnectionString()

        If (String.IsNullOrEmpty(_connectionString)) Then _
            Throw New ProviderException("Empty connection string")

        config.Remove("connectionStringName")

        'Retrive a commandTimeout
        Dim _Timeout As String = config("commandTimeout")
        If String.IsNullOrEmpty(_Timeout) Then
            _Timeout = "30"
        End If
        If Not Integer.TryParse(_Timeout, _CommandTimeout) OrElse _CommandTimeout < 0 Then
            Throw New ProviderException("Value must be non negative integer")
        End If

        config.Remove("commandTimeout")

        'Throw an exception if unrecognized attributes remain
        If (config.Count > 0) Then
            Dim attr As String = config.GetKey(0)
            If (Not String.IsNullOrEmpty(attr)) Then
                Throw New ProviderException("Unrecognized attribute: " + attr)
            End If
        End If

    End Sub

    Public Overrides Property ApplicationName() As String
        Get
            Return Me._applicationName
        End Get
        Set(ByVal value As String)
            If String.IsNullOrEmpty(value) Then
                _applicationName = "/"
            End If
            If (value.Length > 256) Then
                Throw New ProviderException("Provider application name is too long")
            End If
            Me._applicationName = value
        End Set
    End Property

    Public Property ConnectionStringName() As String
        Get
            Return _connectionStringName
        End Get
        Set(ByVal value As String)
            If (String.IsNullOrEmpty(value)) Then _
                Throw New ProviderException("Empty or missing connectionStringName")
            _connectionStringName = value
            If WebConfigurationManager.ConnectionStrings(value) Is Nothing Then _
                Throw New ProviderException("Missing connection string for " & value)
            _connectionString = WebConfigurationManager.ConnectionStrings(value).ConnectionString()
            If (String.IsNullOrEmpty(_connectionString)) Then _
                Throw New ProviderException("Empty connection string for " & value)
        End Set
    End Property

#End Region


#Region "Overriding for Roles"


    Public Overrides Sub DeleteRoleDescription(ByVal Rolename As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_UserInfo_DeleteRoleDescription", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@RoleName", SqlDbType.NVarChar, Rolename))
                cmd.ExecuteNonQuery()
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Update UserInformation NOT success")
            Throw ex
        End Try
    End Sub

    Public Overrides Function GetRoleDescription(ByVal roleName As String) As String
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_UserInfo_GetRoleDescription", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Return Me.GetNullableString(reader, 0)
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Overrides Sub UpdateRoleDescription(ByVal roleName As Object, _
    ByVal desc As Object)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_UserInfo_UpdateRoleDescription", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@RoleName", SqlDbType.NVarChar, roleName))
                cmd.Parameters.Add(Me.CreateInputParam("@Description", SqlDbType.NVarChar, desc))
                cmd.ExecuteNonQuery()
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Update UserInformation NOT success")
            Throw ex
        End Try
    End Sub


#End Region


#Region "Private Subs and Functions"

    Private Function CreateInputParam( _
        ByVal paramName As String, _
        ByVal dbType As SqlDbType, _
        ByVal objValue As Object) As SqlParameter

        Dim p1 As New SqlParameter(paramName, dbType)
        If (objValue Is Nothing) Then
            p1.IsNullable = True
            p1.Value = DBNull.Value
            Return p1
        End If
        p1.Value = objValue
        Return p1
    End Function

    Private Function RoundToSeconds(ByVal dt As DateTime) As DateTime
        Return New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second)
    End Function

    Private Function GetNullableString( _
        ByVal reader As SqlDataReader, _
        ByVal col As Integer) As String
        If reader.IsDBNull(col) Then
            Return String.Empty
        Else
            Return reader.GetString(col)
        End If
    End Function

#End Region



End Class
